package com.drore.cloud.utils;

import com.google.gson.TypeAdapter;
import com.google.gson.reflect.TypeToken;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.util.*;

/***
 * 浙江卓锐科技股份有限公司 版权所有@Copyright 2016
 * 说明  excel 导出通用工具类
 * @author <a href="mailto:baoec@drore.com">baoec@drore.com </a>
 * 2017/09/15 11:43
 */

public class ExportExcelUtil {

    /**
     * 描述：表头  数据
     * 暂时不用
     * @param fieldmap,fileName
     * @return
     * @throws Exception
     */
    public static void ExportExcelByList(HashMap<String, Object> fieldmap, List<HashMap<String,Object>> data, HttpServletResponse response ) throws Exception {
        //创建HSSFWorkbook对象(excel的文档对象)
        HSSFWorkbook wb = new HSSFWorkbook();
        //建立新的sheet对象（excel的表单）
        HSSFSheet sheet=wb.createSheet("表资源");
        //在sheet里创建第一行，参数为行索引(excel的行)，可以是0～65535之间的任何一个
        HSSFRow row1=sheet.createRow(0);
        //设置头部信息
        List<String> heads = new ArrayList<String>(fieldmap.keySet());
        for (int i=0;i<heads.size();i++) {
            row1.createCell(i).setCellValue(heads.get(i));
        }
        //设置内容body
        for (int i=0;i<data.size();i++) {
            //body从第二行开始
            HSSFRow row=sheet.createRow(i+1);
            HashMap<String, Object> map = data.get(i);
            int j = 0;
            for (String head : heads) {
                for (Map.Entry<String, Object> entry : map.entrySet()) {
                    if(entry.getKey().equals(fieldmap.get(head))){
                        row.createCell(j).setCellValue(entry.getValue().toString());
                        j++;
                    }
                }
            }

        }
        //todo  当具体有几个 表的时候 创建多少个 sheet
        for (HashMap<String, Object> resource : data) {
            String r_name = Objects.toString(resource.get("r_name"));
            wb.createSheet(r_name);
        }
/*        //再来一个 合计的  到时候实在需要 再坐成  通用版本
        int hejinum=data.size()+1;
        HSSFRow rowx=sheet.createRow(hejinum);
        rowx.createCell(0).setCellValue("合计");
        for (int x=3;x<=11;x++) {
            String colString = CellReference.convertNumToColString(x);  //长度转成ABC列
            String sixBuf = "SUMPRODUCT("+colString+"2:"+colString+hejinum+"+0)";
            System.out.println(sixBuf);
            rowx.createCell(x).setCellFormula(sixBuf.toString());
        }*/
        //输出Excel文件
        OutputStream output=response.getOutputStream();
        response.reset();
        response.setHeader("Content-disposition", "attachment; filename=allresource.xls");
        response.setContentType("application/msexcel");
        wb.write(output);
        output.close();
        System.out.println("导出成功");
    }
    /**
     * 描述：表头  数据
     * 表资源导出   比较特殊
     * @return
     * @throws Exception
     */
    public static void ExportResource(LinkedHashMap<String, Object> resourceFields,
                                      LinkedHashMap<String, Object> resourceDetailFields,
                                      List<HashMap<String,Object>> data,
                                      List<HashMap<String,Object>> detailData,
                                      HttpServletResponse response ) throws Exception {
        //创建HSSFWorkbook对象(excel的文档对象)
        HSSFWorkbook wb = new HSSFWorkbook();
        //建立新的sheet对象（excel的表单）
        HSSFSheet sheet=wb.createSheet("表资源");
        //在sheet里创建第一行，参数为行索引(excel的行)，可以是0～65535之间的任何一个
        HSSFRow row1=sheet.createRow(0);
        //设置头部信息
        List<String> heads = new ArrayList<String>(resourceFields.keySet());
        for (int i=0;i<heads.size();i++) {
            row1.createCell(i).setCellValue(heads.get(i));
        }
        //设置内容body
        for (int i=0;i<data.size();i++) {
            //body从第二行开始
            HSSFRow row=sheet.createRow(i+1);
            HashMap<String, Object> map = data.get(i);
            int j = 0;
            for (String head : heads) {
                for (Map.Entry<String, Object> entry : map.entrySet()) {
                    if(entry.getKey().equals(resourceFields.get(head))){
                        row.createCell(j).setCellValue(entry.getValue().toString());
                        j++;
                    }
                }
            }

        }
        //todo  当具体有几个 表的时候 创建多少个 sheet
        int i=0;
        for (HashMap<String, Object> resource : data) {
            String r_name = Objects.toString(resource.get("r_name"));
            HSSFSheet sheet_x = wb.createSheet(r_name);
            HashMap<String, Object> stringObjectHashMap = detailData.get(i);
            String resource_detail = Objects.toString(stringObjectHashMap.get("resource_detail"));
            List<HashMap<String,Object>> details = GsonUtil.create().fromJson(resource_detail, new TypeToken<List<HashMap<String,Object>>>(){}.getType());
            DrawingSheet(sheet_x,resourceDetailFields,details);
            i++;
        }

        //输出Excel文件
        OutputStream output=response.getOutputStream();
        response.reset();
        response.setHeader("Content-disposition", "attachment; filename=allresource.xls");
        response.setContentType("application/msexcel");
        wb.write(output);
        output.close();
        System.out.println("导出成功");
    }

    /**
     * 描述： 需要被画的sheet 表头  数据
     * 针对每一个sheet 画表格
     * @param fieldmap,fileName
     * @return
     * @throws Exception
     */
    public static void DrawingSheet(HSSFSheet sheet, HashMap<String, Object> fieldmap, List<HashMap<String, Object>> data){
        //在sheet里创建第一行，参数为行索引(excel的行)，可以是0～65535之间的任何一个
        HSSFRow row1=sheet.createRow(0);
        //设置头部信息
        List<String> heads = new ArrayList<String>(fieldmap.keySet());
        for (int i=0;i<heads.size();i++) {
            row1.createCell(i).setCellValue(heads.get(i));
        }
        //设置内容body
        for (int i=0;i<data.size();i++) {
            //body从第二行开始
            HSSFRow row=sheet.createRow(i+1);
            HashMap<String, Object> map = data.get(i);
            int j = 0;
            for (String head : heads) {
                for (Map.Entry<String, Object> entry : map.entrySet()) {
                    if(entry.getKey().equals(fieldmap.get(head))){
                        row.createCell(j).setCellValue(entry.getValue().toString());
                        j++;
                    }
                }
            }

        }

    }



}
